Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# HIERHEIGHT function

The HIERHEIGHT function returns the value of a node at a specified level for the first value in the current status list of a hierarchical dimension.

To populate a previously-defined relation with the values of a specified hierarchical dimension by level, use the HIERHEIGHT command.

Return Value

The data type returned by HIERHEIGHT is the data type of the dimension value of family-relation.

Syntax

HIERHEIGHT(family-relation [,] level)

Arguments

family-relation

  A child-parent self-relation for the hierarchical dimension. The values of family-relation are the parents.

level

  An `INTEGER` value that represents a level of the hierarchical dimension. The value `1` (one) represents the lowest-level of the hierarchical dimension.

Notes

Limiting the Hierarchical Dimension

The HIERHEIGHT function always returns a single value of the hierarchical dimension. When you do not limit the hierarchical dimension to a single value before calling the HIERHEIGHT function, the HIERHEIGHT function executes against the first value in the current status list of the dimension. Typically, you either limit the hierarchical dimension to a single value before you call the HIERHEIGHT function or you use the HIERHEIGHT function after a FOR statement in order to execute the HIERHEIGHT function for each value of the hierarchical dimension.

Examples

Example 14-11 Using HIERHEIGHT as a Simple Command

Assume that your analytic workspace has a hierarchical dimension named `geography` and a relation named `g0.stanparent` that is a self-relation of the `geography` values for the `Standard` hierarchy of `geography`.

    DEFINE g0.newparent RELATION geography <geography>
    LD Parent-child when hierarchy of geography is 1

Issuing a statement like `REPORT g0.stanparent` displays the values in `g0.stanparent`.

    GEOGRAPHY          G0.STANPARENT
    ---------------- ----------------
    World            NA
    Americas         World
    Canada           Americas
    Toronto          Canada
    Montreal         Canada
    Ottawa           Canada
    ...              ...
    USA              Americas
    Boston           USA
    LosAngeles       USA
    ...              ...
    Mexico           Americas
    Mexicocity       Mexico
    Argentina        Americas
    BuenosAires      Argentina
    Brazil           Americas
    Saopaulo         Brazil
    Colombia         Americas
    Bogota           Colombia
    Australia        World
    East.Aust        Australia
    Sydney           East.Aust
    Madrid           Spain
    Budapest         Hungary
    Athens           Greece
    Vienna           Austria
    Melbourne        East.Aust
    Central.aust     Australia
    Tai-pei          Taiwan
    Singapore        Asia
    Adelaide         Central.Aust
    Bangkok          Thailand
    West.aust        Australia
    Newdelhi         India
    Perth            West.Aust
    Bombay           India
    Malaysia         Asia
    Europe           World
    France           Europe
    Caen             France
    Paris            France

Now you limit GEOGRAPHY to the value `Americas` by issuing the following OLAP DML statement.

    LIMIT geography TO 'Americas'

When you use the HIERHEIGHT function to find the node for `Americas` for the lowest-level of the hierarchy (level 1) by issuing the following OLAP DML statement.

    REPORT HIERHEIGHT(g0.stanparent 1)

The following report is produced.

    HIERHEIGHT(G0.STANPARENT
    COUNTER)
    ------------------------------
    NA

When you use the HIERHEIGHT function to find the node for `Americas` for the highest-level of the hierarchy (level `4`) by issuing the following OLAP DML statement.

    REPORT HIERHEIGHT(g0.stanparent 4)

The following report is produced.

    HIERHEIGHT(G0.STANPARENT
    COUNTER)
    ------------------------------
    World

When you use the HIERHEIGHT function to find the node for `Americas` for the levels `2` and `3` of the hierarchy by issuing the following OLAP DML statements.

    REPORT HIERHEIGHT(g0.stanparent 2)
    REPORT HIERHEIGHT(g0.stanparent 3)

The following reports are produced.

    HIERHEIGHT(G0.STANPARENT
    COUNTER)
    ------------------------------
    NA

    HIERHEIGHT(G0.STANPARENT
    COUNTER)
    ------------------------------
    Americas

Notice that the output for each level corresponds in between the values that are created for a relation created using HIERHEIGHT command. For example, assume you created a relation named `geog.stanhierrel` for the standard hierarchy for `geography` and limit `geography` to '`Americas`. A report of `geog.stanhierrel` would show the same `geography` values for each level.

    LIMIT geography TO 'AMERICAS'
    REPORT DOWN geography geog.stanhierrel

                     ---------------------------GEOG.STANHIERREL--------------------
                     ----------------------------GEOG.LVLDIM------------------------
    GEOGRAPHY               1                2                3                4
    ---------------- ---------------- ---------------- ---------------- ------------
    Americas         NA               NA               Americas         World

Example 14-12 Using HIERHEIGHT After a FOR Statement

Assume that your analytic workspace has a program named `findnodes` that finds the nodes of all of the `geography` values in status.

    DEFINE FINDNODES PROGRAM
    PROGRAM
    VARIABLE level INTEGER
    FOR geography
    DO
    counter = 1
    WHILE counter LE statlen(geog.lvldim)
    DO
    REPORT HIERHEIGHT(g0.stanparent level)
    level = level + 1
    DOEND
    DOEND
    END

Assume also that you limit `geography` to `Americas` and `Asia` and call the HIERHEIGHT function for each level of the `Standard` hierarchy by issuing the following OLAP statements.

    LIMIT geography TO 'Americas', 'Asia'
    CALL findnodes

The output of the `findnodes` program for the `geography` values `Americas` and `Asia` is follows. The program first reports on the value of each level for `Americas` is provided. Then it reports on the value of each level for `Asia`.

    HIERHEIGHT(G0.STANPARENT
    COUNTER)
    ------------------------------
    NA

    HIERHEIGHT(G0.STANPARENT
    COUNTER)
    ------------------------------
    NA

    HIERHEIGHT(G0.STANPARENT
    COUNTER)
    ------------------------------
    Americas

    HIERHEIGHT(G0.STANPARENT
    COUNTER)
    ------------------------------
    World

    HIERHEIGHT(G0.STANPARENT
    COUNTER)
    ------------------------------
    NA

    HIERHEIGHT(G0.STANPARENT
    COUNTER)
    ------------------------------
    NA

    HIERHEIGHT(G0.STANPARENT
    COUNTER)
    ------------------------------
    Asia

    HIERHEIGHT(G0.STANPARENT
    COUNTER)
    ------------------------------
    World

Notice that the output for each level corresponds in between the values that are created for a relation created using the HIERHEIGHT command

    LIMIT geography TO 'Americas' 'Asia'
    REPORT DOWN geography geog.stanhierrel

                     ---------------------------GEOG.STANHIERREL--------------------
                     ----------------------------GEOG.LVLDIM------------------------
    GEOGRAPHY               1                2                3                4
    ---------------- ---------------- ---------------- ---------------- ------------
    Americas         NA               NA               Americas         World
    Asia             NA               NA               Asia             World